<?php
/*  vim: set expandtab shiftwidth=4 softtabstop=4 tabstop=4:    */

class PDO_Mssql extends PDOAbstract implements PDOInterface {

    public function __construct(array $dsn) {
        parent::__construct($dsn, null);
    }

  /*
   *      Primary public function to execute a query
   *      to the database.
   */
	public function query($sql,$fetchMode=PDO::FETCH_ASSOC) {
		//check for limit, do mssql specific limit stuff
		if($limit = self::getLimit($sql)) {
			//erase limit from sql query;
			$sql = str_replace(stristr($sql,'LIMIT'),'',$sql);
			if(is_array($limit)) {
				list($count,$offset) = $limit;
				$sql = self::addLimit($sql,$count,$offset);
			} else {
				$sql = self::addLimit($sql,$limit);
			}
		}

		if(!($sth = self::prepare($sql))) throw new Exception('DB Failed [prepare statement initialization failed]');
		if($fetchMode) $sth->setFetchMode($fetchMode);
		$sth->execute();
		while ($row = $sth->fetch()) $res[] = $row;
		if(isset($res) && is_array($res)) {
			if(count($res) > 1) {
				return $res;
			} else {
				return $res[0];
			}
		}
		if($err = $sth->errorInfo()) {
			if(isset($err[1]) && $err[1] > 0) throw new Exception('Query Failed ('.$sql.') ['.print_r($err,true).']');
		}
		return FALSE;	
	}
  
  
	/* getLimit (this is PDO_Mssql specific)
	 *
	 * @param string $sql
	 * @return mixed [int $count / array($count,$offset) / FALSE]
	 */
  	private function getLimit($sql) {
  		if($pos = strpos($sql,'LIMIT')) {
  			$limit = explode(',',trim(substr($sql,($pos + 6),strlen($sql))));
  			if(count($limit) > 1) return $limit; //count,offset
  			return $limit[0]; //count
  		}
  		return FALSE;
  	}
  	
  	/* addLimit
  	 * 
  	 * @param string $sql
  	 * @param int $count
  	 * @param int $offset (optional)
  	 * @result string $sql
  	 */
  	private function addLimit($sql, $count, $offset=NULL) {
  		if(!is_numeric($offset)) {
  			$sql = preg_replace('/^SELECT\s/i','SELECT TOP '.$count.' ',$sql);
  	  	} else {
  	  		//@todo get order, include in inner and outer table
  	  	  	$orderby = stristr($sql, 'ORDER BY');
  	  	  	if ($orderby !== false) {
  	  	  		$sort = (stripos($orderby, 'desc') !== false) ? 'desc' : 'asc';
 	            $order = str_ireplace('ORDER BY', '', $orderby);
 	            $order = trim(preg_replace('/ASC|DESC/i', '', $order));
  	  	  	}
  	  	  	$sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($count+$offset) . ' ', $sql);
  	  	  	$sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
  	  	  	if ($orderby !== false) {
  	  	  		$sql .= ' ORDER BY ' . $order . ' ';
  	  	  		$sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
  	  	  	}
  	  	  	$sql .= ') AS outer_tbl';
  	  	  	if ($orderby !== false) {
  	  	  		$sql .= ' ORDER BY ' . $order . ' ' . $sort;
			}
  	  	}
  	  	return $sql;
  	}
	
	/*
   *     Insert Prepare wrapper method
   *  @params: type 2d associative array ('field'=>'val')
   *
   */
    public function insert($table,array $params,$extra='') {
        $params = self::clean($params);
        $sql = 'INSERT INTO '.$table.' ';
		$sql .= "(".implode(",",array_keys($params)) .")";
		$sql .= " VALUES ('".implode("','",array_values($params)) ."')";
		if(!($stmt = self::prepare($sql))) throw new Exception('DB Failed [prepare statement initialization failed]');
        $stmt->execute();
        $affected = $stmt->rowCount();
        if($affected > 0) {
            return $affected;
        } else {
			if($err = $stmt->errorInfo()) {
				if(isset($err[2]) && strpos($err[2],'deadlocked')) { //deadlocked
					sleep(1);
					return self::insert($table,$params,$extra);
				}
				if(isset($err[1]) && $err[1] > 0) throw new Exception('Insert Failed - '.$sql.' ['.print_r($err,true).']');
			}
            return FALSE;
        }
    }
    
    public function update($table,array $params,array $matches) {
        $sql = 'UPDATE '.$table.' SET ';
        $rc = count($params);
        $j = 0;
        foreach ($params as $field => $val) {
            $sql .= ' '.$field.' = :'.$field.' ';
            if(($j+1) < $rc) $sql .= ',';
            $j++;
        }
        $sql .= " WHERE";
        $rc = count($matches);
        $j = 0;
        foreach ($matches as $field => $val) {
            $sql .= " ".$field." = '".$val."' ";
            if(($j+1) < $rc) $sql .= 'AND';
            $j++;
        }
        if(!($stmt = self::prepare($sql))) throw new Exception('DB Failed [prepare statement initialization failed]');
        foreach ($params as $field => $val) {
            $stmt->bindValue(':'.$field,$val);
        }
        $stmt->execute();
        $affected = $stmt->rowCount();
        if($affected > 0) {
            return $affected;
        } else {
			if($err = $stmt->errorInfo()) {
				if(isset($err[2]) && strpos($err[2],'deadlocked')) { //deadlocked
					sleep(1);
					return self::update($table,$params,$matches);
				}
				if(isset($err[1]) && $err[1] > 0) throw new Exception('Update Failed ['.print_r($err,true).']');
			}
			return FALSE;
        }
    }
    
    public function delete($table, array $matches) {
        $sql = 'DELETE FROM '.$table.' WHERE';
        $rc = count($matches);
        $j = 0;
        foreach ($matches as $field => $val) {
            $sql .= " ".$field." = '".$val."' ";
            if(($j+1) < $rc) $sql .= 'AND';
            $j++;
        }
        if(!($stmt = self::prepare($sql))) throw new Exception('DB Failed [prepare statement initialization failed]');
        $stmt->execute();
        $affected = $stmt->rowCount();
        if($affected > 0) {
            return $affected;
        } else {
			if($err = $stmt->errorInfo()) {
				if(isset($err[2]) && strpos($err[2],'deadlocked')) { //deadlocked
					sleep(1);
					self::delete($table,$matches);
				}
				if(isset($err[1]) && $err[1] > 0) throw new Exception('Delete Failed ['.print_r($err,true).']');
			}
            return FALSE;
        }
    }
	
	public function fetchFields($table) {
		if($res = self::query("sp_columns $table")) {
			foreach($res as $field) $fields[] = $field['COLUMN_NAME'];
			return $fields;
		}
		return FALSE;
	}
	
	public function getPrimaryKey($table) {
		$sql = "SELECT CU.COLUMN_NAME
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T 
				LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU 
  				  ON CU.CONSTRAINT_CATALOG = T.CONSTRAINT_CATALOG
			      AND CU.CONSTRAINT_SCHEMA = T.CONSTRAINT_SCHEMA
				  AND CU.CONSTRAINT_NAME = T.CONSTRAINT_NAME 
				WHERE T.TABLE_NAME = '$table' AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'";
		if($res = self::query($sql)) return $res['COLUMN_NAME'];
		return FALSE;
	}
	
	public function getForeignKey($table,$pkey) {
		$sql = "SELECT CU.COLUMN_NAME
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T 
				LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU 
  				  ON CU.CONSTRAINT_CATALOG = T.CONSTRAINT_CATALOG
			      AND CU.CONSTRAINT_SCHEMA = T.CONSTRAINT_SCHEMA
				  AND CU.CONSTRAINT_NAME = T.CONSTRAINT_NAME 
				WHERE T.TABLE_NAME = '$table' AND CU.COLUMN_NAME = '$pkey' AND T.CONSTRAINT_TYPE = 'FOREIGN KEY'";
		if($res = self::query($sql)) return $res['COLUMN_NAME'];
		return FALSE;
	}
    
    public function lastInsertedID() {
        if($res = DB::$db->query('SELECT @@IDENTITY as id')) return $res['id'];
        return FALSE;
    }

    public function transaction($params) {
        switch($params) {
            case 'begin':
                //self::beginTransaction();
                self::query('BEGIN TRAN');
            break;
            case 'commit':
                //self::commit();
                self::query('COMMIT TRAN');
            break;
            case 'rollback':
                //self::rollBack();
                self::query('ROLLBACK TRAN');
            break;
        }
    }
    
}
?>
